﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Maticsoft.DBUtility;

/// <summary>
/// 动态拼串sql，操作数据类
/// </summary>
public class DBControl
{
    /// <summary>
    /// 动态添加数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="dic">参数和对应值集合</param>
    /// <returns>新添加数据id</returns>
    public int InsertDB(string tableName, Dictionary<string, object> dic)
    {
        StringBuilder columns = new StringBuilder();
        StringBuilder columnParameters = new StringBuilder();
        List<SqlParameter> sqlParams = new List<SqlParameter>();
        if (dic.Count > 0)
        {
            foreach (KeyValuePair<string, object> keyDic in dic)
            {
                columns.AppendFormat(",{0}",keyDic.Key);
                columnParameters.AppendFormat(",@{0}" , keyDic.Key);
                sqlParams.Add(new SqlParameter(keyDic.Key, keyDic.Value));
            }
        }
        else
        {
            return 0;
        }

        string sql =
            string.Format("insert into {0}({1}) values ({2});select @@IDENTITY",
            tableName, columns.ToString().Substring(1), columnParameters.ToString().Substring(1));

        object obj = DbHelperSQL.GetSingle(sql, sqlParams.ToArray());
        if (obj == null)
        {
            return 0;
        }
        else
        {
            return Convert.ToInt32(obj);
        }
    }

    /// <summary>
    /// 动态更新数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="dic">参数和对应值集合</param>
    /// <param name="dicWhere">where条件参数和对应值集合</param>
    /// <returns>是否更新成功！</returns>
    public bool UpdateDB(string tableName, Dictionary<string, object> dic, Dictionary<string, object> dicWhere)
    {
        StringBuilder columns = new StringBuilder();
        StringBuilder wheresSql = new StringBuilder();
        List<SqlParameter> sqlParams = new List<SqlParameter>();
        if (dic!=null && dic.Count > 0)
        {
            foreach (KeyValuePair<string, object> keyDic in dic)
            {
                columns.AppendFormat(",{0}=@{0}" , keyDic.Key);
                sqlParams.Add(new SqlParameter(keyDic.Key, keyDic.Value));
            }
        }
        else
        {
            return false;
        }

        if (dicWhere!=null && dicWhere.Count > 0)
        {
            foreach (KeyValuePair<string, object> keyDic in dicWhere)
            {
                wheresSql.AppendFormat(" and {0}=@{0}" , keyDic.Key);
                sqlParams.Add(new SqlParameter(keyDic.Key, keyDic.Value));
            }
        }
        else
        {
            return false;
        }

        string sql = 
            string.Format("update {0} set {1} where 1=1 {2}", 
            tableName, columns.ToString().Substring(1), wheresSql.ToString().Substring(1));

        return DbHelperSQL.ExecuteSql(sql, sqlParams.ToArray()) > 0;
    }

    /// <summary>
    /// 动态更新数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="dic">参数和对应值集合</param>
    /// <param name="whereSql">where 条件(注意sql注入攻击)</param>
    /// <returns>是否更新成功！</returns>
    public bool UpdateDB(string tableName, Dictionary<string, object> dic, string whereSql)
    {
        StringBuilder columns = new StringBuilder();

        List<SqlParameter> sqlParams = new List<SqlParameter>();
        if (dic!=null && dic.Count > 0)
        {
            foreach (KeyValuePair<string, object> keyDic in dic)
            {
                columns.AppendFormat(",{0}=@{0}", keyDic.Key);
                sqlParams.Add(new SqlParameter(keyDic.Key, keyDic.Value));
            }
        }
        else
        {
            return false;
        }



        string sql = 
            string.Format("update {0} set {1} where {2}",
                tableName, columns.ToString().Substring(1), whereSql);

        return DbHelperSQL.ExecuteSql(sql, sqlParams.ToArray()) > 0;
    }


    /// <summary>
    /// 查询是否存在此记录
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="dicWhere">条件</param>
    /// <returns></returns>
    public bool Exists(string tableName, Dictionary<string, object> dicWhere)
    {
        string sql = string.Format("select count(1) from {0} where 1=1 ", tableName);

        SqlParameter[] sqlParams;

        string whereSql = dicFill_where(dicWhere, out sqlParams);

        return DbHelperSQL.Exists(sql + whereSql, sqlParams);
    }

    /// <summary>
    /// 查询是否存在此记录数
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="dicWhere">条件</param>
    /// <returns></returns>
    public int ExistNums(string tableName, Dictionary<string, object> dicWhere)
    {
        string sql = string.Format("select count(1) from {0} where 1=1 ", tableName);

        SqlParameter[] sqlParams;

        string whereSql = dicFill_where(dicWhere, out sqlParams);

        return int.Parse(DbHelperSQL.Query(sql + whereSql, sqlParams).Tables[0].Rows[0][0].ToString());
    }

    /// <summary>
    /// 简单查询数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columns">字段</param>
    /// <param name="dicWhere">条件</param>
    /// <returns></returns>
    public DataTable GetDataTable(string tableName, string columns, Dictionary<string, object> dicWhere)
    {
        string sql = string.Format("select {0} from {1} ", columns, tableName);

        SqlParameter[] sqlParams;

        if (dicWhere != null)
        {
            string whereSql = dicFill_where(dicWhere, out sqlParams);

            return DbHelperSQL.Query(sql + " where 1=1 " + whereSql, sqlParams).Tables[0];
        }
        else
        {
            return DbHelperSQL.Query(sql).Tables[0];
        }
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sqlStr">sql(要有占位符"{0}"才能条件筛选数据)</param>
    /// <param name="dicWhere">条件</param>
    /// <returns></returns>
    public DataTable GetDataTableBySql(string sqlStr, Dictionary<string, object> dicWhere)
    {
        return GetDataTableBySql(sqlStr, dicWhere, string.Empty);
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sqlStr">sql(要有占位符"{0}"才能条件筛选数据)</param>
    /// <param name="dicWhere">条件</param>
    /// <param name="whereSql">where 条件(注意sql注入攻击)</param>
    /// <returns></returns>
    public DataTable GetDataTableBySql(string sqlStr, Dictionary<string, object> dicWhere, string whereSql)
    {
        SqlParameter[] sqlParams;

        if (dicWhere != null)
        {
            string _whereSql = dicFill_where(dicWhere, out sqlParams) + whereSql;

            return DbHelperSQL.Query(string.Format(sqlStr, " where 1=1 " + _whereSql), sqlParams).Tables[0];
        }
        else
        {
            return DbHelperSQL.Query(string.IsNullOrEmpty(whereSql) ? sqlStr : sqlStr + whereSql).Tables[0];
        }
    }


    /// <summary>
    /// 封装where参数sql语句
    /// </summary>
    /// <param name="dicWhere"></param>
    /// <param name="par"></param>
    /// <returns></returns>
    private string dicFill_where(Dictionary<string, object> dicWhere, out SqlParameter[] par)
    {
        StringBuilder whereSql = new StringBuilder();
        List<SqlParameter> sqlParams = new List<SqlParameter>();
        if (dicWhere.Count > 0)
        {
            foreach (KeyValuePair<string, object> keyDic in dicWhere)
            {
                if (keyDic.Value.ToString().IndexOf("%", StringComparison.Ordinal) >-1 ||
                    keyDic.Value.ToString().IndexOf("_", StringComparison.Ordinal) > -1 ||
                    (keyDic.Value.ToString().IndexOf("[", StringComparison.Ordinal) > -1 && keyDic.Value.ToString().IndexOf("]", StringComparison.Ordinal) > -1)
                    )//只要是含有 % _  [ ]  都是按照模糊查询
                {
                    whereSql.AppendFormat(" and {0} like @{0} " , keyDic.Key);
                }
                else
                {
                    whereSql.AppendFormat(" and {0} = @{0} ", keyDic.Key);
                }
               
                sqlParams.Add(new SqlParameter(keyDic.Key, keyDic.Value));
            }
        }
        par = sqlParams.ToArray();

        return whereSql.ToString();
    }

}